package common;

import basic.QuerySet;

public class SqlHandler {

	public final static String regex = "--|insert|delete|update|union|create|drop|truncate|alter|grant|execute|exec|xp_cmdshell|call|declare|source|column";

	//只允许查询
	public static String filter(String param) {
		return param.replaceAll("(?i)" + regex, ""); // (?i)不区分大小写替换
	}

	public static void main(String[] args) {
		StringBuffer sb = new StringBuffer();
		sb.append("select ");
		sb.append(" DATE_FORMAT(txn_date,'%Y-%m-%d') 'txnDate',");
		sb.append(" LEFT(txn_no,6) 'subNo',");
		sb.append(" count(case when txn_state='1' then 1 else null end) 'stateCount',");
		sb.append(" sum(case when txn_state='1' then  txn_amount else 0 end) 'stateTotal',");
		sb.append(" count(case when txn_refund='1' then 1 else null end) 'refundCount',");
		sb.append(" sum(case when txn_refund='1' then  txn_amount else 0 end) 'refundTotal',");
		sb.append(" count(case when txn_refuse='1' then 1 else null end) 'refuseCount',");
		sb.append(" sum(case when txn_refuse='1' then  txn_amount else 0 end) 'refuseTotal',");
		sb.append(" count(case when txn_freeze='1' then 1 else null end) 'freezeCount',");
		sb.append(" sum(case when txn_freeze='1' then  txn_amount else 0 end) 'freezeTotal'");
		sb.append(" from t_transaction ");
		sb.append(" where txn_no like CONCAT('7777','%')");
		sb.append(" group by LEFT(txn_no,6),DATE_FORMAT(txn_date,'%Y-%m-%d')");
		sb.append(" order by DATE_FORMAT(txn_date,'%Y-%m-%d') desc");
		QuerySet<Object> querySet = new QuerySet<Object>();
		querySet.setSqlInfo(sb.toString());
		System.out.println(filter(sb.toString()));
	}
}
